import pymysql
import pandas as pd
import pymysql.cursors

class MysqlUtils(object):
    """数据库工具类
    
    Args:
        object (_type_): _description_
    """
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='tushare',
            port=3306,
            charset='utf8'
        )
        
class classIfication(object):
    """分类相关类
    
    Args:
        object (_type_): _description_
    """
    
    def __init__(self):
        pass
    
    def get_fina_indicator(self,conn):
        """获取财务数据
        """
        
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        
        sql = """
        SELECT ts_code,ann_date,eps,total_revenue_ps,undist_profit_ps,gross_margin,fcff,fcfe,tangible_asset,bps,grossprofit_margin,
        npta FROM financial_data WHERE ann_date >= '2023-01-01' and ann_date < '2024-01-01'
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        df1 = df.dropna(subset=['eps','total_revenue_ps','undist_profit_ps','gross_margin','fcff','fcfe','tangible_asset','bps','grossprofit_margin','npta'])
        # 重建索引
        df1 = df1.reset_index(drop=True)
        
        return df1
    
    def get_daily(self,conn,df):
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        new_list = []
        
        for i in range(len(df['ts_code'])):
            ann_date_str = df['ann_date'][i].strftime('%Y%m%d')
            
            sql = "SELECT trade_date,closes FROM date_1 WHERE ts_code = \'" + df['ts_code'][i] +" \' and trade_date > Date(\' " + ann_date_str + "\') order by trade_date asc limit 20"
            cursor.execute(sql)
            ret = cursor.fetchall()
            df1 = pd.DataFrame(ret)
            print(i)
            # break
            try:
                if len(df1) > 0 :
                    max_close = df1['closes'].max()
                    min_close = df1['closes'].min()
                    the_close = df1['closes'].iloc()
                    
                    new_list.append({
                        'ts_code':df['ts_code'][i],
                        'ann_date':df['ann_date'][i],
                        'max_close':max_close,
                        'min_close':min_close,
                        'the_close':the_close,
                        'eps':df['eps'][i],
                        'total_revenue_ps':df['total_revenue_ps'][i],
                        'undist_profit_ps':df['undist_profit_ps'][i],
                        'gross_profit_ps':df['gross_profit_ps'][i],
                        'fcff':df['fcff'][i],
                        'fcfe':df['fcfe'][i],
                        'tangible_asset':df['tangible_asset'][i],
                        'bps':df['bps'][i],
                        'grossprofit_margin':df['grossprofit_margin'][i],
                        'npta':df['npta'][i],
                    })
            except Exception as e:
                pass
        
        df2 = pd.DataFrame(new_list)
        df2.to_csv('dailv.csv',index=False)
if __name__ == '__main__':
    mu = MysqlUtils()
    ci = classIfication()
    df = ci.get_fina_indicator(mu.conn)
    ci.get_daily(mu.conn,df)